﻿Imports System.Data.SqlClient


Public Class clspedidos
    Public Function ArmaPedidos(ByVal conexion As String) As DataSet

        Try
            'Esta funcion recibe como parametro una conexion y retorna un dataset
            Dim conn As New SqlConnection(conexion)
            Dim sql As String = "Select * From dbo.NOTAS ORDER BY codnota"
            Dim dsPedidos As New DataSet
            Dim daPedidos As New SqlDataAdapter(sql, conn)
            conn.Open()
            daPedidos.Fill(dsPedidos, "dbo.NOTAS")
            conn.Close()
            Return dsPedidos

        Catch err As SqlException
            Throw err
        Catch err As Exception
            Throw err
        End Try

    End Function
    Public Function ArmaClientes(ByVal conexion As String) As DataSet
        Try
            'Esta funcion recibe como parametro una conexion y retorna un dataset
            Dim conn As New SqlConnection(conexion)
            Dim sql As String = "Select codcliente, nombre From clientes ORDER BY codcliente"
            Dim dsClientes As New DataSet
            Dim daclientes As New SqlDataAdapter(sql, conn)
            conn.Open()
            daclientes.Fill(dsClientes, "CLIENTES")
            conn.Close()
            Return dsClientes
        Catch err As SqlException
            Throw err
        Catch err As Exception
            Throw err
        End Try

    End Function
    Public Function ArmaEstadoNota(ByVal conexion As String) As DataSet
        Try
            'Esta funcion recibe como parametro una conexion y retorna un dataset
            Dim conn As New SqlConnection(conexion)
            Dim sql As String = "Select * From ESTADONOTA"
            Dim dsClientes As New DataSet
            Dim daclientes As New SqlDataAdapter(sql, conn)
            conn.Open()
            daclientes.Fill(dsClientes, "ESTADONOTA")
            conn.Close()
            Return dsClientes
        Catch err As SqlException
            Throw err
        Catch err As Exception
            Throw err
        End Try

    End Function
    Public Sub Incluir(ByVal nronota As Integer, ByVal codcliente As Integer, ByVal fecha As Date, ByVal _
                           estado As Integer, ByVal contacto As String, ByVal total As Integer, ByVal tiponota As Integer, ByVal _
                           direccion As String, ByVal fechavencimiento As Date, ByVal conexion As String)

        Try
            Dim conn As New SqlConnection(conexion)
            Dim cmd As SqlCommand
            Dim sqlbuilder As New System.Text.StringBuilder
            With sqlbuilder
                .Append("INSERT INTO NOTAS")
                .Append("(nronota, codcliente, fecha, estado, contacto, total, tiponota, direccion, fechavencimiento)")
                .Append("VALUES('")
                .Append("nronota='" & nronota & "',")
                .Append("codcliente='" & codcliente & "',")
                .Append("fecha='" & fecha & "',")
                .Append("estado='" & estado & "',")
                .Append("contacto='" & contacto & "',")
                .Append("total='" & total & "',")
                .Append("tiponota='" & tiponota & "',")
                .Append("direccionentrega='" & direccion & "',")
                .Append("fechavencimiento='" & fechavencimiento & "'")

                conn = New SqlConnection(conexion)
                conn.Open()
                cmd = New SqlCommand(sqlbuilder.ToString, conn)
                cmd.ExecuteNonQuery()
                conn.Close()
                cmd.Dispose()
                conn.Dispose()
            End With
        Catch err As SqlException
            Throw err
        Catch err As Exception
            Throw err
        End Try


    End Sub
    Public Sub Actualizar(ByVal nronota As Integer, ByVal codcliente As Integer, ByVal fecha As Date, ByVal _
                          estado As Integer, ByVal contacto As String, ByVal total As Integer, ByVal tiponota As Integer, ByVal _
                          direccion As String, ByVal fechavencimiento As Date, ByVal conexion As String)

        Try
            Dim conn As New SqlConnection(conexion)
            Dim cmd As SqlCommand
            Dim sqlbuilder As New System.Text.StringBuilder
            With sqlbuilder
                .Append("UPDATE NOTAS SET ")
                .Append("(@nronota, @codcliente, @fecha, @estado, @contacto, @total, @tiponota, @direccion, @fechavencimiento)")
                .Append("VALUES('")
                .Append(nronota & "', '")
                .Append(codcliente & "', '")
                .Append(fecha & "', '")
                .Append(estado & "', '")
                .Append(contacto & "', '")
                .Append(total & "', '")
                .Append(tiponota & "', '")
                .Append(direccion & "', '")
                .Append(fechavencimiento & "')")
                conn = New SqlConnection(conexion)
                conn.Open()
                cmd = New SqlCommand(sqlbuilder.ToString, conn)
                cmd.ExecuteNonQuery()
                conn.Close()
                cmd.Dispose()
                conn.Dispose()
            End With
        Catch err As SqlException
            Throw err
        Catch err As Exception
            Throw err
        End Try

    End Sub

    Public Sub Excluir(ByVal codnota As Integer, ByVal conexion As String)

        Try
            Dim conn As New SqlConnection(conexion)
            Dim cmd As SqlCommand
            Dim sql As String = "DELETE FROM NOTAS WHERE codnota='" & codnota & "'"
            conn = New SqlConnection(conexion)
            conn.Open()
            cmd = New SqlCommand(sql, conn)
            cmd.ExecuteNonQuery()
            conn.Close()
            cmd.Dispose()
            conn.Dispose()
        Catch err As SqlException
            Throw err
        Catch err As Exception
            Throw err
        End Try

    End Sub


    Public Function ArmaDetalle(ByVal conexion As String) As DataSet
        Try
            'Esta funcion recibe como parametro una conexion y retorna un dataset
            Dim conn As New SqlConnection(conexion)
            Dim sql As String = ("SELECT codnota, descripcion, cantidad, precio  FROM dbo.DETALLENOTAS JOIN dbo.[PRODUCTOS] ON dbo.[DETALLENOTAS].codproducto= dbo.PRODUCTOS.codproducto")
            Dim dsdetalle As New DataSet
            Dim dadetalle As New SqlDataAdapter(sql, conn)
            conn.Open()
            dadetalle.Fill(dsdetalle, "DETALLENOTAS")
            conn.Close()
            Return dsDetalle
        Catch err As SqlException
            Throw err
        Catch err As Exception
            Throw err
        End Try

    End Function

End Class
